#!/use/bin/python
# coding=utf-8
# 主动服务 开机率 analysis_startup_rate
import datetime
import pymysql
import redis

from dbutils.pooled_db import PooledDB



# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 大数据 57 数据源
def getBigDataConnection():
    pool = PooledDB(pymysql, 1, host='192.168.1.57', user='superset', passwd='vCKsEJJex2',
                    db='Cboard', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

def updateFlag1():
    try:
        conn, cur = getConfConnection()
        conn1, cur1 = getDataConnection()
        # 修改 analysis_subscribe_number 数据
        selectSql = f"""select t.active_id from log_common_service_{yyMMddDate} t where t.`key` = 'saEnterPage' """
        cur1.execute(selectSql)
        selectResult = cur1.fetchall()
        updateSql = "update life_assistant_devices set switches_state1 = switches_state1 |(1 << (16 - 1)) where 1=1"
        delNum = 1
        dataStr = ""
        #1万条数据更新一次
        for result in selectResult:
            id = result[0]
            dataStr = f"{dataStr} '{str(id)}',"
            delNum  = delNum+1
            if delNum>10000:
                updateSql2 = f"{updateSql} and active_id in ( {dataStr[:-1]} )"
                print(updateSql2)
                cur.execute(updateSql2)
                conn.commit()
                delNum = 1
                dataStr= ""
        #剩余的数据
        updateSql2 = f"{updateSql} and active_id in ( {dataStr[:-1]} )"
        print(updateSql2)
        cur.execute(updateSql2)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def updateFlag2():
    try:
        conn, cur = getConfConnection()
        # 修改 analysis_subscribe_number 数据
        r = getReidsConnection()
        updateSql = "update life_assistant_devices set switches_state1 = switches_state1 |(1 << (16 - 1)) where 1=1"
        delNum = 1
        dataStr = ""
        key = f"LOG_DEVICE_ACTIVE_ZMGN_"+date

        #1万条数据更新一次
        for value in r.smembers(key):
            id = value
            dataStr = f"{dataStr} '{str(id)}',"
            delNum  = delNum+1
            if delNum>10000:
                updateSql2 = f"{updateSql} and active_id in ( {dataStr[:-1]} )"
                print(updateSql2)
                cur.execute(updateSql2)
                conn.commit()
                delNum = 1
                dataStr= ""
        #剩余的数据
        if delNum > 1:
            updateSql2 = f"{updateSql} and active_id in ( {dataStr[:-1]} )"
            print(updateSql2)
            cur.execute(updateSql2)
            conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    return re_date

if __name__ == '__main__':
    yyMMddDate = getdateyyMMdd(0)
    date = getdate(1)
    updateFlag1()
    updateFlag2()